#include "chelperclass.h"
#include "QSqlQuery"
#include "QSqlRecord"
#include "QVariant"
#include <QLocale>
#include <QRegExp>
#include <QDate>



CHelperClass::CHelperClass(QObject *parent)
    : QObject(parent)
	, m_vkupenPriliv(0.f)
	, m_vkupenOdliv(0.f)
{

}

CHelperClass::~CHelperClass()
{

}

bool CHelperClass::isKomintentExists(QString &komName)
{
    bool stat = false;
    QString temp = "select id, naziv, adresa, tel, mobil, zirosmetka, edb, deponent, sifdejnost, mb, zabeleska1, zabeleska2, rabat, grad from komintenti where naziv like '%";
    temp += komName;
    temp += "%' ";
    QSqlQuery query(temp);
    while (query.next()) 
    {
        if (query.value(query.record().indexOf("naziv")).toString() == komName)
        {
            stat = true;
        }
    } 
    return stat;
}

QString  CHelperClass::getKomintentName(QString m_id)
{
	QString prevoznikIme = "";
	QString temp = "select naziv from komintenti where id = ";
	temp += m_id;
	
	QSqlQuery query(temp);
	while (query.next()) 
	{
		prevoznikIme = query.value(0).toString();
	} 
	return prevoznikIme;
}

bool CHelperClass::isArtikliExists(QString &artName)
{
    bool stat = false;
    QString temp = "select id, sifra, artikal, edm, ref, kataloski_broj, "
        "art_nabavna_cena, art_nabavna_cena_valuta,  "
        "art_prodazna_cena, art_prodazna_cena_valuta "
        " from artikli where artikal like '%";
    temp += artName;
    temp += "%' ";
    QSqlQuery query(temp);
    while (query.next()) 
    {
        if (query.value(query.record().indexOf("artikal")).toString() == artName)
        {
            stat = true;
        }
    } 
    return stat;
}


int CHelperClass::genSifraArtikal()
{
    int maxArt = -1;
    QString temp = "select sifra from artikli ";
    QSqlQuery query(temp);
    while (query.next()) 
    {
        maxArt = maxArt > query.value(query.record().indexOf("sifra")).toInt() ? maxArt : query.value(query.record().indexOf("sifra")).toInt();
    } 
    return maxArt + 1;
}

bool CHelperClass::isBankExists(QString &bankName)
{
    bool stat = false;
    QString temp = "select  BankIme"
        " from tbank where BankIme like '%";
    temp += bankName;
    temp += "%' ";
    QSqlQuery query(temp);
    while (query.next()) 
    {
        if (query.value(query.record().indexOf("BankIme")).toString() == bankName)
        {
            stat = true;
        }
    } 
    return stat;
}

bool CHelperClass::isFakturaExists(QString &fakturaId, QString& tip )
{
    bool stat = false;
	QString temp = "select  dok_id"
		" from dokumenti_finansii where dok_id = ";
	temp += fakturaId;
	temp += " and dok_tip = ";
    temp += tip;
    temp += "";

    QSqlQuery query(temp);
	while (query.next()) 
	{
		if (query.value(query.record().indexOf("dok_id")).toString() == fakturaId)
		{
			stat = true;
		}
	} 
	return stat;
}


void CHelperClass::fakturaStatus(QStandardItem *item,  QString& tip)
{
	QString fakturaBroj = item->text();
	float vk_iznos = 0;
	// 	getFakturaPlatenIznos(fakturaBroj, tip);
	//     float vk_iznos = getFakturaVkupenIznos(fakturaBroj, tip);	
	bool okInt;
	m_plateno = 0;
	vk_iznos = 0;
	QMap<int, float>::iterator itPlateno = m_mapPlateno.find(fakturaBroj.toInt(&okInt, 10));
	if (itPlateno != m_mapPlateno.end()) 
	{
		m_plateno = itPlateno.value();
	}

	QMap<int, float>::iterator itFakturirano = m_mapZadolzuvanje.find(fakturaBroj.toInt(&okInt, 10));
	if (itFakturirano != m_mapZadolzuvanje.end()) 
	{
		vk_iznos = itFakturirano.value();
	}


	float razlika = vk_iznos - m_plateno;
	if (vk_iznos != 0 && abs(vk_iznos - m_plateno) < 0.1)
	{
		QIcon ic1(":/sterna/Resources/ok.png");
		item->setIcon(ic1);
		item->setToolTip(trUtf8("Платена "));
	}
	else if ((m_plateno > 0) && ((vk_iznos - m_plateno) >= 0.1))
	{
		QIcon ic1(":/sterna/Resources/information.png");
		item->setIcon(ic1);
		item->setToolTip(trUtf8("Делумно платена "));
	}
	else if ((m_plateno == 0) && (vk_iznos > 0))
	{
		QIcon ic1(":/sterna/Resources/stop.png");
		item->setIcon(ic1);
		item->setToolTip(trUtf8("Неплатена "));
	}
	else if (vk_iznos <= 0)
	{
		QIcon ic1(":/sterna/Resources/error.png");
		item->setIcon(ic1);
		item->setToolTip(trUtf8("Невалидна "));
	}
	else if (m_plateno > vk_iznos )
	{
		QIcon ic1(":/sterna/Resources/error.png");
		item->setIcon(ic1);
		item->setToolTip(trUtf8("Невалидна "));
	}

}

void CHelperClass::getFakturaPlatenIznos(QString& faktBroj, QString& tip)
{
	QString temp = "select  sum(priliv), sum(odliv) "
		" from tstavka where Faktura_ID   = ";
	temp += faktBroj;
	temp += " and Izvod_Tip = ";
	temp += tip;
	temp += "";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		m_vkupenPriliv = query.value(0).toFloat();
		m_vkupenOdliv = query.value(1).toFloat();
	}
    m_plateno = tip == "40" ? m_vkupenPriliv : m_vkupenOdliv;
}

float CHelperClass::getPlatenIznos()
{
    return m_plateno;
}

float CHelperClass::getFakturaVkupenIznos(QString& faktBroj, QString& tip)
{
	float vkupenIznos = 0.f;
	QString temp = 
		" select sum((ikol * icena * (100 - rabat) / 100))  "
		" from finansovi_stavki m                                            "
		" left join artikli a on m.artikal_id = a.id                "
		" where dok_id = ";

	temp += faktBroj;
	temp += " and dok_tip = ";
	temp += tip;
	temp += "";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		vkupenIznos = query.value(0).toFloat();
	}
	return vkupenIznos;
}


QString CHelperClass::getArtikalByID(QString text)
{
	int artikal_id = text.toInt();
	QSqlQuery query;
	query.prepare("select artikal from artikli where id = :id");
	query.bindValue(":id", artikal_id);	
	query.exec();
	query.next();
	return (query.value(0).toString());
}

float CHelperClass::getVkKol(QString& artikal_ID, QString& tip)
{
	float vkKolicina = 0.f;
	QString temp = "select  sum(ikol) "
		" from magacin where artikal_id   = ";
	temp += artikal_ID;
	temp += " and dok_tip = ";
	temp += tip;
	temp += "";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		vkKolicina = query.value(0).toFloat();
	}
	return vkKolicina;
}


float CHelperClass::getVkKolBetween(QString& artikal_ID, QString& tip, QString& tip1)
{
	float vkKolicina = 0.f;
	QString temp = "select  sum(ikol) "
		" from magacin where sub_tip >= 0 and artikal_id   = ";
	temp += artikal_ID;
	temp += " and dok_tip between ";
	temp += tip;
	temp += " and ";
	temp += tip1;

	QSqlQuery query(temp);
	while (query.next()) 
	{
		vkKolicina = query.value(0).toFloat();
	}
	return vkKolicina;
}


float CHelperClass::getVkKolByDate(QString& artikal_ID, QString& tip, QDate & mDate, QDate & mDate2)
{
	float vkKolicina = 0.f;
	QString temp = "";
	QString temp_tip = "";
	if (tip == "20")
	{
		temp = " select sum(ikol) from dokumenti "
			" left join magacin "
			" on dokumenti.dok_tip = magacin.dok_tip and "
			" dokumenti.dok_id = magacin.dok_id "
			" where dokumenti.dok_tip between 20 and 22 "
			" and artikal_id = " + artikal_ID +
			" and datum between #" + mDate.toString("yyyy/MM/dd") + "# and #" +  mDate2.toString("yyyy/MM/dd") + "#";
	}
	else
	{
		temp = " select sum(ikol) from dokumenti "
			" left join magacin "
			" on dokumenti.dok_tip = magacin.dok_tip and "
			" dokumenti.dok_id = magacin.dok_id "
			" where dokumenti.dok_tip = 40 "
			" and artikal_id = " + artikal_ID +
			" and datum between #" + mDate.toString("yyyy/MM/dd") + "# and #" +  mDate2.toString("yyyy/MM/dd") + "#";
	}
	
	QSqlQuery query(temp);
	while (query.next()) 
	{
		vkKolicina = query.value(0).toFloat();
	}
	return vkKolicina;
}


void CHelperClass::createStatusMap(QString& tip)
{
	createPlatenoMap(tip);
	createZadolzuvanjeMap(tip);
}

void CHelperClass::createPlatenoMap(QString& tip)
{
	m_mapPlateno.clear();
	int fakt_id = 0;
	float vkupenPriliv = 0.f;
	float vkupenOdliv = 0.f;

	QString temp = 

		" select Dok_Id, sum(IznosPlaten) "
		" from Izvod_Plakanje "
		" where Dok_Tip = "+ tip +
		" group by Dok_Tip, Dok_Id ";


	QSqlQuery query(temp);
	while (query.next()) 
	{
		fakt_id = query.value(0).toInt();
		vkupenPriliv = query.value(1).toFloat();
		vkupenOdliv = query.value(1).toFloat();
		if (tip == "40")
		{
			m_mapPlateno.insert(fakt_id, vkupenPriliv);
		} 
		else
		{
			m_mapPlateno.insert(fakt_id, vkupenOdliv);
		}
	}
}
void CHelperClass::createZadolzuvanjeMap(QString& tip)
{
	m_mapZadolzuvanje.clear();
	int fakt_id = 0;
	float vkupenIznos = 0.f;

	QString temp = ""; 
	if (tip != "21")
	{
		temp = " select dok_id, sum((ikol*icena*(100-rabat)/100))  ";
	}
	else
	{
		temp = " select dok_id, sum((ikol*icena*(100-rabat)/100))  ";
	}
	temp += " from finansovi_stavki m                                            "
		" left join artikli a on m.artikal_id = a.id "
		" where dok_tip = " + tip +
		" group by dok_id, dok_tip    ";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		fakt_id = query.value(0).toInt();
		vkupenIznos = query.value(1).toFloat();
		m_mapZadolzuvanje.insert(fakt_id, vkupenIznos);
	}
}

void CHelperClass::createIzlezniFakturiMap()
{
	m_mapIzlezniFakturiStatus.clear();
	int fakt_id = 0;
	float vkupenPriliv = 0.f;
	float vkupenOdliv = 0.f;

	QString temp = 
		"SELECT  Izvod_Tip, Faktura_Id, SUM(priliv) "
		"FROM tstavka "
		"WHERE Izvod_Tip = 40 "
		"GROUP BY Izvod_Tip, Faktura_ID ";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		fakt_id = query.value(1).toInt();
		vkupenPriliv = query.value(2).toFloat();
		m_mapIzlezniFakturiStatus.insert(fakt_id, vkupenPriliv);
	}
}
void CHelperClass::createVlezniFakturiMap()
{
	m_mapVlezniFakturiStatus.clear();
	int fakt_id = 0;
	float vkupenOdliv = 0.f;

	QString temp = 
		"SELECT  Izvod_Tip, Faktura_Id, SUM(odliv) "
		"FROM tstavka "
		"WHERE Izvod_Tip = 20 "
		"GROUP BY Izvod_Tip, Faktura_ID ";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		fakt_id = query.value(1).toInt();
		vkupenOdliv = query.value(2).toFloat();
		m_mapVlezniFakturiStatus.insert(fakt_id, vkupenOdliv);
	}
}
void CHelperClass::createVlezniFakturiIznosMap()
{
	m_mapVlezniFakturiIznos.clear();
	int fakt_id = 0;
	float vkupenIznosZaPlakanje = 0.f;
	QString temp = 
		" SELECT dok_id, sum((ikol * icena * (100 - rabat) / 100))  "
		" FROM finansovi_stavki "
		" WHERE dok_tip = 20 "
		" GROUP BY dok_id ";
	QSqlQuery query(temp);
	while (query.next()) 
	{
		fakt_id = query.value(0).toInt();
		vkupenIznosZaPlakanje = query.value(1).toFloat();
		m_mapVlezniFakturiIznos.insert(fakt_id, vkupenIznosZaPlakanje);	
	}
}
void CHelperClass::createIzlezniFakturiIznosMap()
{
	m_mapIzlezniFakturiIznos.clear();
	int fakt_id = 0;
	float vkupenIznosZaPlakanje = 0.f;
	QString temp = 
		" SELECT dok_id, sum((ikol * icena * (100 - rabat) / 100))  "
		" FROM finansovi_stavki "
		" WHERE dok_tip = 40 "
		" GROUP BY dok_id ";
	QSqlQuery query(temp);
	while (query.next()) 
	{
		fakt_id = query.value(0).toInt();
		vkupenIznosZaPlakanje = query.value(1).toFloat();
		m_mapIzlezniFakturiIznos.insert(fakt_id, vkupenIznosZaPlakanje);	
	}
}


void CHelperClass::getFakturaStatus(QStandardItem *item,  QString& tip)
{
	
	int fakturaBroj = item->text().toInt();
// 	getFakturaPlatenIznos(fakturaBroj, tip);
// 	float vk_iznos = getFakturaVkupenIznos(fakturaBroj, tip);	
	float vk_iznos = 0.f;
	m_plateno = 0.f;
	if (tip == "20")
	{
		QMap<int, float>::iterator itPlateno = m_mapVlezniFakturiStatus.find(fakturaBroj);
		if (itPlateno != m_mapVlezniFakturiStatus.end()) 
		{
			m_plateno = itPlateno.value();
		}

		QMap<int, float>::iterator itFakturirano = m_mapVlezniFakturiIznos.find(fakturaBroj);
		if (itFakturirano != m_mapVlezniFakturiIznos.end()) 
		{
			vk_iznos = itFakturirano.value();
		}
	} 
	else if(tip == "40")
	{
		QMap<int, float>::iterator itPlateno = m_mapIzlezniFakturiStatus.find(fakturaBroj);
		if (itPlateno != m_mapIzlezniFakturiStatus.end()) 
		{
			m_plateno = itPlateno.value();
		}

		QMap<int, float>::iterator itFakturirano = m_mapIzlezniFakturiIznos.find(fakturaBroj);
		if (itFakturirano != m_mapIzlezniFakturiIznos.end()) 
		{
			vk_iznos = itFakturirano.value();
		}
	}
	else
	{
		return;
	}

	float razlika = vk_iznos - m_plateno;
	if (vk_iznos != 0 && abs(vk_iznos - m_plateno) < 0.01)
	{
		QIcon ic1(":/sterna/Resources/ok.png");
		item->setIcon(ic1);
		item->setToolTip(trUtf8("Платена Фактура"));
	}
	else if ((m_plateno > 0) && ((vk_iznos - m_plateno) >= 0.01))
	{
		QIcon ic1(":/sterna/Resources/information.png");
		item->setIcon(ic1);
		item->setToolTip(trUtf8("Делумно платена Фактура"));
	}
	else if ((m_plateno == 0) && (vk_iznos > 0))
	{
		QIcon ic1(":/sterna/Resources/stop.png");
		item->setIcon(ic1);
		item->setToolTip(trUtf8("Неплатена Фактура"));
	}
	else if (vk_iznos <= 0)
	{
		QIcon ic1(":/sterna/Resources/error.png");
		item->setIcon(ic1);
		item->setToolTip(trUtf8("Невалидна Фактура"));
	}
	else if (m_plateno > vk_iznos )
	{
		QIcon ic1(":/sterna/Resources/error.png");
		item->setIcon(ic1);
		item->setToolTip(trUtf8("Невалидна Фактура"));
	}
}

void CHelperClass::getArtikalStatus(QStandardItem *item)
{
    bool ok;
    int proizvod_id = item->text().toInt(&ok, 10);
    bool stat = false;
    QMap<int, int>::iterator itProizvod = m_mapArtikliSoNormativStatus.find(proizvod_id);
    if (itProizvod != m_mapArtikliSoNormativStatus.end()) 
    {
        stat = true;
    }

    if (stat)
    {
        QIcon ic1(":/sterna/Resources/ok.png");
        item->setIcon(ic1);
        item->setToolTip(trUtf8("Артикалот има норматив"));
    }
}

bool CHelperClass::getArtikalHasNormativ(QString artikalId)
{
	bool ok;
	int proizvod_id = artikalId.toInt(&ok, 10);
	bool stat = false;
	QMap<int, int>::iterator itProizvod = m_mapArtikliSoNormativStatus.find(proizvod_id);
	if (itProizvod != m_mapArtikliSoNormativStatus.end()) 
	{
		stat = true;
	}
	return stat;
}

QStringList CHelperClass::checkUser(QString &userName, QString &userPass)
{
	QStringList info;
	QSqlQuery query;
	query.prepare("select id, userName, pass, role "
	" from user where userName = :a and pass =:b");
	
	query.bindValue(":a", userName);
	query.bindValue(":b", userPass);
	query.exec();

	while (query.next()) 
	{
		info << query.value(0).toString() 
			 << query.value(1).toString() 
			 << query.value(2).toString() 
			 << query.value(3).toString(); 
	} 
	return info;
}


//////////////////////////////////////////////////////////////////

void CHelperClass::createSaldoMap(int tip)
{
	if(tip)
	{
		createSaldoProizvodiPrilivMap();  //22 24
		createSaldoProizvodiOdlivMap(); // 40
	}
	else
	{
		createSaldoArtikliPrilivMap();  //20
		createSaldoArtikliOdlivMap();  // 41 44
	}
}



void CHelperClass::createSaldoMapAll()
{
		createSaldoProizvodiPrilivMapAll();  // 20 22 24 
		createSaldoArtikliOdlivMapAll();  // 40 41 44
}

void CHelperClass::createSaldoMapAll_Prodavnica()
{
	createSaldoProizvodiPrilivMapAll_Prodavnica();  // 20 22 24 
	createSaldoArtikliOdlivMapAll_Prodavnica();  // 40 41 44
}

void CHelperClass::createSaldoMapAll_ProdavnicaByArtikal(QString &artikal_id)
{
	createSaldoProizvodiPrilivMapAll_ProdavnicaByArtikal(artikal_id);  // 20 22 24 
	createSaldoArtikliOdlivMapAll_ProdavnicaByArtikal(artikal_id);  // 40 41 44
}


void CHelperClass::createSaldoArtikliPrilivMap()
{
	m_mapSaldoArtikliPrilivStatus.clear();
	int art_id = 0;
	float vkupenPrilivArtikal = 0.f;
	QString temp = 

		" select  artikal_id, sum(ikol) "
		" from magacin "
		" where dok_tip = 20 "
		" group by artikal_id ";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		art_id = query.value(0).toInt();
		vkupenPrilivArtikal = query.value(1).toFloat();
		m_mapSaldoArtikliPrilivStatus.insert(art_id, vkupenPrilivArtikal);	
	}
}
void CHelperClass::createSaldoArtikliOdlivMap()
{
	m_mapSaldoArtikliOdlivStatus.clear();
	int art_id = 0;
	float vkupenOdlivArtikal = 0.f;
	QString temp = 

		" select  artikal_id, sum(ikol) "
		" from magacin "
        " where (dok_tip = 41 or dok_tip = 44) "
		" group by artikal_id ";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		art_id = query.value(0).toInt();
		vkupenOdlivArtikal = query.value(1).toFloat();
		m_mapSaldoArtikliOdlivStatus.insert(art_id, vkupenOdlivArtikal);	
	}
}
void CHelperClass::createSaldoProizvodiPrilivMap()
{
	m_mapSaldoProizvodiPrilivStatus.clear();
	int art_id = 0;
	float vkupenPrilivProizvod = 0.f;
	QString temp = 

	" select  artikal_id, sum(ikol) "
	" from magacin "
    " where (dok_tip = 22 or dok_tip = 24)"
	" group by artikal_id ";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		art_id = query.value(0).toInt();
		vkupenPrilivProizvod = query.value(1).toFloat();
		m_mapSaldoProizvodiPrilivStatus.insert(art_id, vkupenPrilivProizvod);	
	}
}
void CHelperClass::createSaldoProizvodiOdlivMap()
{
	m_mapSaldoProizvodiOdlivStatus.clear();
	int art_id = 0;
	float vkupenOdlivProizvod = 0.f;
	QString temp = 

		" select  artikal_id, sum(ikol) "
		" from magacin "
		" where dok_tip = 40 "
		" group by artikal_id ";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		art_id = query.value(0).toInt();
		vkupenOdlivProizvod = query.value(1).toFloat();
		m_mapSaldoProizvodiOdlivStatus.insert(art_id, vkupenOdlivProizvod);	
	}
}
float CHelperClass::getLagerLista(QString & item,  QString& tip)
{
	int artikalBroj = item.toInt();
	float result = 0.f;
	float priliv = 0.f;
	float odliv = 0.f;

	if (tip == "ART_SALDO")
	{
		QMap<int, float>::iterator itPlateno = m_mapSaldoArtikliPrilivStatus.find(artikalBroj);
		if (itPlateno != m_mapSaldoArtikliPrilivStatus.end()) 
		{
			priliv = itPlateno.value();
		}

		QMap<int, float>::iterator itFakturirano = m_mapSaldoArtikliOdlivStatus.find(artikalBroj);
		if (itFakturirano != m_mapSaldoArtikliOdlivStatus.end()) 
		{
			odliv = itFakturirano.value();
		}

		result = priliv - odliv;
	} 

	else if(tip == "PRZ_SALDO")
	{
		QMap<int, float>::iterator itPlateno = m_mapSaldoProizvodiPrilivStatus.find(artikalBroj);
		if (itPlateno != m_mapSaldoProizvodiPrilivStatus.end()) 
		{
			priliv = itPlateno.value();
		}

		QMap<int, float>::iterator itFakturirano = m_mapSaldoProizvodiOdlivStatus.find(artikalBroj);
		if (itFakturirano != m_mapSaldoProizvodiOdlivStatus.end()) 
		{
			odliv = itFakturirano.value();
		}

		result = priliv - odliv;
	}
	else if (tip == "ART_PRILIV")
	{
		QMap<int, float>::iterator itPlateno = m_mapSaldoArtikliPrilivStatus.find(artikalBroj);
		if (itPlateno != m_mapSaldoArtikliPrilivStatus.end()) 
		{
			result = itPlateno.value();
		}
	}
	else if (tip == "ART_ODLIV")
	{
		QMap<int, float>::iterator itPlateno = m_mapSaldoArtikliOdlivStatus.find(artikalBroj);
		if (itPlateno != m_mapSaldoArtikliOdlivStatus.end()) 
		{
			result = itPlateno.value();
		}
	}
	else if (tip == "PRZ_PRILIV")
	{
		QMap<int, float>::iterator itPlateno = m_mapSaldoProizvodiPrilivStatus.find(artikalBroj);
		if (itPlateno != m_mapSaldoProizvodiPrilivStatus.end()) 
		{
			result = itPlateno.value();
		}
	}
	else if (tip == "PRZ_ODLIV")
	{
		QMap<int, float>::iterator itPlateno = m_mapSaldoProizvodiOdlivStatus.find(artikalBroj);
		if (itPlateno != m_mapSaldoProizvodiOdlivStatus.end()) 
		{
			result = itPlateno.value();
		}
	}

	return result;
}

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

int CHelperClass::calcMaxIdTblDokumenti(QString sTip)
{
    int m_id = -1;
    QString temp = "select max(dok_id) from dokumenti where dok_tip = "+ sTip;
    QSqlQuery query(temp);
    QString country1 = "";
    int fieldNo = query.record().indexOf("max(dok_id)");
    while (query.next()) 
    {
        m_id = query.value(0).toInt() + 1;
    }
    return m_id;
}

int CHelperClass::calcMaxIdTblMagacin(QString sTip)
{
    int m_id = -1;
    QString temp = "select max(dok_id) from magacin where dok_tip = "+ sTip;
    QSqlQuery query(temp);
    QString country1 = "";
    int fieldNo = query.record().indexOf("max(dok_id)");
    while (query.next()) 
    {
        m_id = query.value(0).toInt() + 1;
    }
    return m_id;
}


int CHelperClass::calcMaxIdTblKalkulacii()
{
	int m_id = -1;
	QString temp = "select max(dok_id) from kalkulacii ";
	QSqlQuery query(temp);
	QString country1 = "";
	int fieldNo = query.record().indexOf("max(dok_id)");
	while (query.next()) 
	{
		m_id = query.value(0).toInt() + 1;
	}
	return m_id;
}

int CHelperClass::calcMaxIdTblKalkulaciiStavki()
{
	int m_id = -1;
	QString temp = "select max(dok_id) from kalkulacii_stavki ";
	QSqlQuery query(temp);
	QString country1 = "";
	int fieldNo = query.record().indexOf("max(dok_id)");
	while (query.next()) 
	{
		m_id = query.value(0).toInt() + 1;
	}
	return m_id;
}



bool CHelperClass::checkIfArtikalIsProizvod(QString artikalId)
{
    bool res = false;
    QSqlQuery query;
    query.clear();
    query.prepare("SELECT count(id_proizvod) FROM proizvodi_sostav WHERE id_proizvod = " + artikalId + " ");
    query.exec();

    int countProizvodi = 0;
    while (query.next()) 
    {
        countProizvodi = query.value(0).toInt();
    }
    res = countProizvodi > 0 ? true : false;
    return res;
}

void CHelperClass::createArtikliSoNormativMap()
{
    m_mapArtikliSoNormativStatus.clear();
    int proizvod_id;
    QString temp = 
        " SELECT DISTINCT id_proizvod "
        " FROM proizvodi_sostav ";

    QSqlQuery query(temp);
    while (query.next()) 
    {
        proizvod_id = query.value(0).toInt();
        m_mapArtikliSoNormativStatus.insert(proizvod_id, proizvod_id);
    }
}


bool CHelperClass::isArtikalGrouped(QString artikal_id)
{
    bool result = false;
    QSqlQuery query;
    query.clear();
    query.prepare("SELECT kategorija FROM artikli WHERE sifra = " + artikal_id + " ");
    query.exec();

    while (query.next()) 
    {
        result = query.value(0).toInt() > 0 ? true : false;
    }
    return result;
}

void CHelperClass::getListofArtikalGrouped(std::map<QString, tArt> &artMap, QStringList & listKey)
{
	QSqlQuery query;
    query.clear();
    query.prepare("SELECT id, sifra, artikal, edm FROM artikli WHERE kategorija = 1");
    query.exec();

    while (query.next()) 
    {
//        list << query.value(0).toString();
		tArt item;
		item.art_id = query.value(0).toString();
		item.art_sifra = query.value(1).toString();
		QString itemArtikal = query.value(2).toString();
		item.art_name = itemArtikal;
		item.art_edm = query.value(3).toString();
		artMap.insert(std::pair<QString, tArt>(itemArtikal, item));
		listKey << itemArtikal;
    }
}

void CHelperClass::createListaIzlezniFakturiIznosi(QString &tip)
{
    m_mapFakturi.clear();
    QString dok_id = "";
    fakturiData dataFakt;

    QString temp = 
        " select finansovi_stavki.dok_tip, finansovi_stavki.dok_id,sum(ikol * icena * (1 - rabat/100) / ( 1 + artikli.ddv / 100)), "
        " sum(ikol * icena * (1 - rabat/100) -  ikol * icena * (1 - rabat/100) / ( 1 + artikli.ddv / 100)) " 
        " from finansovi_stavki "
        " left join artikli  on finansovi_stavki.artikal_id = artikli.id "
        " group by finansovi_stavki.dok_tip, finansovi_stavki.dok_id "
        " having finansovi_stavki.dok_tip = " + tip +" ";
    QSqlQuery query(temp);
    while (query.next()) 
    {
        dok_id = query.value(1).toString();
        dataFakt.iznos = query.value(2).toFloat();
        dataFakt.ddv = query.value(3).toFloat();
        dataFakt.vkupno = dataFakt.iznos + dataFakt.ddv;

        m_mapFakturi.insert(dok_id, dataFakt);
    }

}
float CHelperClass::getFakturaIznos(QString& dok_id )
{
    float iznos = 0.0f;
    QMap <QString, fakturiData>::iterator it = m_mapFakturi.find(dok_id);
    if (it != m_mapFakturi.end()) 
    {
        iznos = it.value().iznos;
    }
    return iznos;
}

float CHelperClass::getFakturaDDV(QString& dok_id )
{
    float ddv = 0.0f;
    QMap <QString, fakturiData>::iterator it = m_mapFakturi.find(dok_id);
    if (it != m_mapFakturi.end()) 
    {
        ddv = it.value().ddv;
    }
    return ddv;
}

float CHelperClass::getFakturaVkupno(QString& dok_id )
{
    float vkupno = 0.0f;
    QMap <QString, fakturiData>::iterator it = m_mapFakturi.find(dok_id);
    if (it != m_mapFakturi.end()) 
    {
        vkupno = it.value().vkupno;
    }
    return vkupno;
}

double CHelperClass::rouded(double num)
{ 
    double res = 0;	
    int tempNum = int(num);
    if ((num - tempNum) < 0.50)
    {
        res = (double)tempNum;
    }
    else
    {
        res = double(tempNum + 1);
    }
    return res;
}


void CHelperClass::createSaldoProizvodiPrilivMapAll()
{
	m_mapPriliv.clear();
	QString art_id = "";
	float vkupenPrilivProizvod = 0.f;
	QString temp = 

		" SELECT magacin.artikal_id, magacin.mag_id, SUM(ikol) "
		" FROM dokumenti "  
		" LEFT JOIN magacin ON (dokumenti.dok_id = magacin.dok_id  AND dokumenti.dok_tip_link = magacin.dok_tip_link ) "
		" WHERE (magacin.dok_tip = 20 OR magacin.dok_tip = 22 OR magacin.dok_tip = 24) "
		" GROUP BY magacin.artikal_id, magacin.mag_id ";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		art_id = query.value(0).toString() + "M" +query.value(1).toString();
		vkupenPrilivProizvod = query.value(2).toFloat();
		m_mapPriliv.insert(art_id, vkupenPrilivProizvod);	
	}
}

void CHelperClass::createSaldoArtikliOdlivMapAll()
{
	m_mapOdliv.clear();
	QString art_id = "";
	float vkupenOdlivArtikal = 0.f;
	QString temp = 
		" SELECT magacin.artikal_id, magacin.mag_id, SUM(ikol) "
		" FROM dokumenti "  
		" LEFT JOIN magacin ON (dokumenti.dok_id = magacin.dok_id  AND dokumenti.dok_tip_link = magacin.dok_tip_link ) "
		" WHERE (magacin.dok_tip = 40 OR magacin.dok_tip = 41 OR magacin.dok_tip = 44) "
		" GROUP BY magacin.artikal_id, magacin.mag_id ";
	QSqlQuery query(temp);
	while (query.next()) 
	{
		art_id = query.value(0).toString() + "M" + query.value(1).toString();
		vkupenOdlivArtikal = query.value(2).toFloat();
		m_mapOdliv.insert(art_id, vkupenOdlivArtikal);	
	}
}

float CHelperClass::getLagerListaAll(QString & item,  QString& tip, QString & mag_id)
{
	QString artikalBroj = item + "M" + mag_id;
	float result = 0.f;
	float priliv = 0.f;
	float odliv = 0.f;

	if (tip == "PRILIV")
	{
		QMap<QString, float>::iterator itPriliv = m_mapPriliv.find(artikalBroj);
		if (itPriliv != m_mapPriliv.end()) 
		{
			priliv = itPriliv.value();
		}
		result = priliv;
	} 

	else if(tip == "ODLIV")
	{
		QMap<QString, float>::iterator itOdliv = m_mapOdliv.find(artikalBroj);
		if (itOdliv != m_mapOdliv.end()) 
		{
			odliv = itOdliv.value();
		}

// 		QMap<QString, float>::iterator itFakturirano = m_mapSaldoProizvodiOdlivStatus.find(artikalBroj);
// 		if (itFakturirano != m_mapSaldoProizvodiOdlivStatus.end()) 
// 		{
// 			odliv = itFakturirano.value();
// 		}
		result = odliv;
	}
	return result;
}

void CHelperClass::createSaldoProizvodiPrilivMapAll_Prodavnica()
{
	m_mapPriliv_Prodavnica.clear();
	int art_id = 0;
	float vkupenPrilivProizvod = 0.f;
	QString temp = 

		" SELECT magacin.artikal_id, SUM(kol) "
		" FROM dokumenti_prodavnica dokumenti "  
		" LEFT JOIN magacin_prodavnica magacin ON (dokumenti.dok_id = magacin.dok_id  AND dokumenti.dok_tip = magacin.dok_tip ) "
		" WHERE magacin.dok_tip = 20  "
		" GROUP BY magacin.artikal_id ";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		art_id = query.value(0).toInt();
		vkupenPrilivProizvod = query.value(1).toFloat();
		m_mapPriliv_Prodavnica.insert(art_id, vkupenPrilivProizvod);	
	}
}

void CHelperClass::createSaldoArtikliOdlivMapAll_Prodavnica()
{
	m_mapOdliv_Prodavnica.clear();
	int art_id = 0;
	float vkupenOdlivArtikal = 0.f;
	QString temp = 
		" SELECT magacin.artikal_id, SUM(kol) "
		" FROM dokumenti_prodavnica dokumenti"  
		" LEFT JOIN magacin_prodavnica magacin ON (dokumenti.dok_id = magacin.dok_id  AND dokumenti.dok_tip = magacin.dok_tip ) "
		" WHERE (magacin.dok_tip = 40 or magacin.dok_tip = 23) "
		" GROUP BY magacin.artikal_id ";
	QSqlQuery query(temp);
	while (query.next()) 
	{
		art_id = query.value(0).toInt();
		vkupenOdlivArtikal = query.value(1).toFloat();
		m_mapOdliv_Prodavnica.insert(art_id, vkupenOdlivArtikal);	
	}
}

float CHelperClass::getLagerListaAll_Prodavnica(QString & item,  QString& tip)
{
	int artikalBroj = item.toInt();
	float result = 0.f;
	float priliv = 0.f;
	float odliv = 0.f;

	if (tip == "PRILIV")
	{
		QMap<int, float>::iterator itPriliv = m_mapPriliv_Prodavnica.find(artikalBroj);
		if (itPriliv != m_mapPriliv_Prodavnica.end()) 
		{
			priliv = itPriliv.value();
		}
		result = priliv;
	} 

	else if(tip == "ODLIV")
	{
		QMap<int, float>::iterator itOdliv = m_mapOdliv_Prodavnica.find(artikalBroj);
		if (itOdliv != m_mapOdliv_Prodavnica.end()) 
		{
			odliv = itOdliv.value();
		}

		result = odliv;
	}
	return result;
}


void CHelperClass::createSaldoProizvodiPrilivMapAll_ProdavnicaByArtikal(QString &artikalId)
{
	m_mapPriliv_ProdavnicaArtikal.clear();
	int prod_id = 0;
	float vkupenPrilivProizvod = 0.f;
	QString temp = 

		" SELECT prodavnica_id, SUM(kol) "
		" FROM dokumenti_prodavnica dokumenti "  
		" LEFT JOIN magacin_prodavnica magacin ON (dokumenti.dok_id = magacin.dok_id  AND dokumenti.dok_tip = magacin.dok_tip ) "
		" WHERE magacin.dok_tip = 20  and magacin.artikal_id =" + artikalId +
		" GROUP BY prodavnica_id ";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		prod_id = query.value(0).toInt();
		vkupenPrilivProizvod = query.value(1).toFloat();
		m_mapPriliv_ProdavnicaArtikal.insert(prod_id, vkupenPrilivProizvod);	
	}
}

void CHelperClass::createSaldoArtikliOdlivMapAll_ProdavnicaByArtikal(QString &artikalId)
{
	m_mapOdliv_ProdavnicaArtikal.clear();
	int prod_id = 0;
	float vkupenOdlivArtikal = 0.f;
	QString temp = 
		" SELECT prodavnica_id, SUM(kol) "
		" FROM dokumenti_prodavnica dokumenti"  
		" LEFT JOIN magacin_prodavnica magacin ON (dokumenti.dok_id = magacin.dok_id  AND dokumenti.dok_tip = magacin.dok_tip ) "
		" WHERE (magacin.dok_tip = 40 or magacin.dok_tip = 23) and magacin.artikal_id =" + artikalId +
		" GROUP BY prodavnica_id ";
	QSqlQuery query(temp);
	while (query.next()) 
	{
		prod_id = query.value(0).toInt();
		vkupenOdlivArtikal = query.value(1).toFloat();
		m_mapOdliv_ProdavnicaArtikal.insert(prod_id, vkupenOdlivArtikal);	
	}
}

float CHelperClass::getLagerListaAll_ProdavnicaArtikal(QString & item,  QString& tip)
{
	int ProdBroj = item.toInt();
	float result = 0.f;
	float priliv = 0.f;
	float odliv = 0.f;

	if (tip == "PRILIV")
	{
		QMap<int, float>::iterator itPriliv = m_mapPriliv_ProdavnicaArtikal.find(ProdBroj);
		if (itPriliv != m_mapPriliv_ProdavnicaArtikal.end()) 
		{
			priliv = itPriliv.value();
		}
		result = priliv;
	} 

	else if(tip == "ODLIV")
	{
		QMap<int, float>::iterator itOdliv = m_mapOdliv_ProdavnicaArtikal.find(ProdBroj);
		if (itOdliv != m_mapOdliv_ProdavnicaArtikal.end()) 
		{
			odliv = itOdliv.value();
		}
		result = odliv;
	}
	return result;
}


bool CHelperClass::isArtikalExist_CenovnikProdavnica(QString &artikalId, QString& prodavnicaID)
{
	bool stat = false;
	QString temp = 
		" SELECT artikal_id "
		" FROM magacin_prodavnica_cenovnik"  
		" WHERE prodavnica_id = " + prodavnicaID + " and artikal_id = " + artikalId; 
	QSqlQuery query(temp);
	while (query.next()) 
	{
		stat = true;
	}
	return stat;
}

QString CHelperClass::GetRandomString()
{
	const QString possibleCharacters("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789");
		const int randomStringLength = 12; // assuming you want random strings of 12 characters

	QString randomString;
	for(int i=0; i<randomStringLength; ++i)
	{
		int index = qrand() % possibleCharacters.length();
		QChar nextChar = possibleCharacters.at(index);
		randomString.append(nextChar);
	}
	return randomString;
}

int CHelperClass::calcMaxIdTblFinansoviNalozi()
{
	int m_id = -1;
	QString temp = " select max(nalog_br) from FinansoviNalozi ";
	QSqlQuery query(temp);
	while (query.next()) 
	{
		m_id = query.value(0).toInt() + 1;
	}
	return m_id;
}

int CHelperClass::calcMaxIdTblFinansoviNaloziStavki()
{
	int m_id = -1;
	QString temp = " select max(nalog_br) from FinansoviNalozi_stavki ";
	QSqlQuery query(temp);
	while (query.next()) 
	{
		m_id = query.value(0).toInt() + 1;
	}
	return m_id;
}


float CHelperClass::vkIznosNalogSurovini(QString &nalog_br)
{
	float vkIznos = 0.f;
	QString temp = " SELECT SUM(ikol * icena) FROM `magacin` WHERE dok_tip = 41 AND dok_id = ";
	temp += nalog_br;
	temp += " ";
	QSqlQuery query(temp);
	while (query.next()) 
	{
		vkIznos = query.value(0).toFloat();
	} 
	return vkIznos;
}

float CHelperClass::vkIznosNalogProizvodi(QString &nalog_br)
{
	float vkIznos = 0.f;
	QString temp = " SELECT SUM(ikol * icena) FROM `magacin` WHERE dok_tip = 22 AND dok_id = ";
	temp += nalog_br;
	temp += " ";
	QSqlQuery query(temp);
	while (query.next()) 
	{
		vkIznos = query.value(0).toFloat();
	} 
	return vkIznos;
}

QString CHelperClass::getPlatenIznosZaFaktura( QString& tip, QString &faktBr)
{
	QString resA;
	QString temp = 
		" select Dok_Tip, Dok_Id, sum(IznosPlaten) "
		" from Izvod_Plakanje "
		" where Dok_Tip = "+ tip +" and Dok_Id = " + faktBr +
		" group by Dok_Tip, Dok_Id ";
	QSqlQuery query(temp);
	while (query.next()) 
	{
		resA = query.value(2).toFloat();
	}
	return resA;
}

bool CHelperClass::isIzvodExists(QString &bankaId, QString &izvodId)
{
	bool stat = false;
	QString temp = " select IzvodBr from Izvodi where BankaId  = " + bankaId + " and IzvodBr =" + izvodId;
	QSqlQuery query(temp);
	while (query.next()) 
	{
		if (query.value(query.record().indexOf("IzvodBr")).toString() == izvodId)
		{
			stat = true;
		}
	} 
	return stat;
}

void CHelperClass::createPovratnicaFakturiIznosi()
{
	m_mapPovratnici.clear();
	QString dok_id = "";
	fakturiData dataFakt;

	QString temp = 
		" select  IzleznaFakturaBroj, sum(IznosVraten) "
		" from povratniciPlakanje "
		" group by IzleznaFakturaBroj ";
	QSqlQuery query(temp);
	while (query.next()) 
	{
		dok_id = query.value(0).toString();
		dataFakt.vkupno = query.value(1).toFloat();
		dataFakt.iznos = 0;
		dataFakt.ddv = 0;
		m_mapPovratnici.insert(dok_id, dataFakt);
	}
}

void CHelperClass::getFakturaIznosiAll( QString& tip)
{
	mapIznosi.clear();
	QString temp = 

		" select Dok_Id, sum(IznosPlaten) "
		" from Izvod_Plakanje "
		" where Dok_Tip = "+ tip +
		" group by Dok_Tip, Dok_Id ";

	QSqlQuery query(temp);
	while (query.next()) 
	{
		QString m_fakturiId = query.value(0).toString();
		float m_vkupenPriliv = query.value(1).toFloat();
		float m_vkupenOdliv = query.value(1).toFloat();

		tdataInfo temp;
		temp.priliv = m_vkupenPriliv;
		temp.odliv = m_vkupenOdliv;
		mapIznosi.insert(m_fakturiId, temp);
	}
}

float CHelperClass::getFakturaPrilivIznos(QString& dok_id )
{
	float iznos = 0.0f;
	QMap <QString, tdataInfo>::iterator it = mapIznosi.find(dok_id);
	if (it != mapIznosi.end()) 
	{
		iznos = it.value().priliv;
	}
	return iznos;
}

float CHelperClass::getFakturaOdlivIznos(QString& dok_id )
{
	float iznos = 0.0f;
	QMap <QString, tdataInfo>::iterator it = mapIznosi.find(dok_id);
	if (it != mapIznosi.end()) 
	{
		iznos = it.value().odliv;
	}
	return iznos;
}

float CHelperClass::getPovratnicaFakturaVkupno(QString& dok_id )
{
	float vkupno = 0.0f;
	QMap <QString, fakturiData>::iterator it = m_mapPovratnici.find(dok_id);
	if (it != m_mapPovratnici.end()) 
	{
		vkupno = it.value().vkupno;
	}
	return vkupno;
}

QStringList CHelperClass::getVraboteniInfo(QString& u, QString& p)
{
	QStringList info;
	QString fakt_br = "";
	QString temp = 
		" select id,korisnicko_ime, pasword, ime, prezime, adresa, grad, tel, uloga, user_id "
		" from vraboteni "
		" where korisnicko_ime ='"+u+"' and pasword = '"+p+"'";
	QSqlQuery query(temp);
	while (query.next()) 
	{
		info << query.value(0).toString() 
			<< query.value(1).toString() 
			<< query.value(2).toString() 
			<< query.value(3).toString() 
			<< query.value(4).toString() 
			<< query.value(5).toString() 
			<< query.value(6).toString() 
			<< query.value(7).toString() 
			<< query.value(8).toString() 
			<< query.value(9).toString() 
			;
	}
	return info;
}

QString CHelperClass::getVrabotenNextID()
{
	QString user_br = "";
	QString temp = 
		" select max (user_id) +1 as nextID "
		" from vraboteni "
		" where user_id < 9000";
	QSqlQuery query(temp);
	while (query.next()) 
	{
		user_br =  query.value(0).toString() ;
	}
	return user_br;
}